/**
 * Copyright(c) by JNet Smartware Group.
 * All rights reserved. Not distribution or redistribution without
 * any permission of JNet Smartware Group.
 * 
 * @author thuc
 * 2010/07/31
 */
package vn.smartware.co.server.dao.business;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import vn.smartware.co.commons.Const;
import vn.smartware.co.dto.BillDTO;
import vn.smartware.co.dto.MstRoomDTO;
import vn.smartware.co.dto.ReserveDetailDTO;
import vn.smartware.co.dto.ReserveDto;
import vn.smartware.co.forms.ReserveForm;
import vn.smartware.co.framework.bean.LoginDTO;
import vn.smartware.co.framework.exception.ServerException;
import vn.smartware.co.server.sql.factory.AbstractReserveSQLManager;

/**
 * @author thuctt
 * ReserveSQLManager
 */
public class ReserveSQLManager extends AbstractReserveSQLManager {

	private static Log log = LogFactory.getLog(ReserveSQLManager.class);

	/**
	 * makeGetReserveInfoSQL
	 * @param room
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void makeGetReserveInfoSQL(MstRoomDTO room, LoginDTO user,
			Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT ")
		.append(" RESV.RESERVE_ID, ")
		.append(" RESV.ROOM_NO, ")
		.append(" ROOM.ROOM_NAME, ")
		.append(" ROOM.ROOM_TYPE, ")
		.append(" CLS_ROOM_TYPE.NAME AS ROOM_TYPE_NAME, ")
		.append(" ROOM.STATUS AS ROOM_STATUS, ")
		.append(" CLS_ROOM_STATUS.NAME AS ROOM_STATUS_NAME, ")
		.append(" RESV.RENT_TYPE, ")
		.append(" CLS_RENT_TYPE.NAME AS RENT_TYPE_NAME, ")
		.append(" RESV.BOOKING_YMD, ")
		.append(" RESV.BOOKING_TIME, ")
		.append(" RESV.INUSE_YMD, ")
		.append(" RESV.INUSE_TIME, ")
		.append(" RESV.FINISH_YMD, ")
		.append(" RESV.FINISH_TIME, ")
		.append(" RESV.NOTE, ")
		.append(" RESV.CUSTOMER_NO, ")
		.append(" RESV.CURRENCY, ")
		.append(" CUS.NAME AS CUSTOMER_NAME, ")
		.append(" CUS.ID_NUMBER, ")
		.append(" CUS.TEL, ")
		.append(" BIL.START_YMD, ")
		.append(" BIL.START_TIME, ")
		.append(" BIL.END_YMD, ")
		.append(" BIL.END_TIME, ")
		.append(" BIL.PAY_MONEY ")
		
		.append(" FROM RESERVE RESV ")
		
		.append(" INNER JOIN MSTROOM ROOM  ")
		.append(" ON ROOM.ROOM_NO = RESV.ROOM_NO ")
		.append(" AND ROOM.DELETE_DATE IS NULL ")
		
		.append(" LEFT OUTER JOIN MSTCUSTOMER CUS ")
		.append(" ON CUS.CUSTOMER_NO = RESV.CUSTOMER_NO ")
		.append(" AND CUS.DELETE_DATE IS NULL ")
		
		.append(" LEFT OUTER JOIN BILL BIL")
		.append(" ON RESV.RESERVE_ID = BIL.RESERVE_ID AND BIL.DELETE_DATE IS NULL ")
		
		.append(" LEFT OUTER JOIN TYPECLASS CLS_ROOM_TYPE ")
		.append(" ON CLS_ROOM_TYPE.KEY1 = 'ROOM_TYPE' ")
		.append(" AND CLS_ROOM_TYPE.VALUE = ROOM.ROOM_TYPE ")
		
		.append(" LEFT OUTER JOIN TYPECLASS CLS_ROOM_STATUS ")
		.append(" ON CLS_ROOM_STATUS.KEY1 = 'ROOM_STATUS' ")
		.append(" AND CLS_ROOM_STATUS.VALUE = ROOM.STATUS ")
		
		.append(" LEFT OUTER JOIN TYPECLASS CLS_RENT_TYPE ")
		.append(" ON CLS_RENT_TYPE.KEY1 = 'RENT_TYPE' ")
		.append(" AND CLS_RENT_TYPE.VALUE = RESV.RENT_TYPE ")
		
		.append(" WHERE RESV.DELETE_DATE IS NULL ")
		.append(" AND RESV.RESERVE_ID = ")
		.append(" (SELECT MAX(RES.RESERVE_ID)  ")
		.append(" FROM RESERVE RES  ")
		.append(" INNER JOIN MSTROOM TMPROOM ")
		.append(" ON TMPROOM.ROOM_NO = RES.ROOM_NO  ")
		.append(" AND TMPROOM.DELETE_DATE IS NULL  ")
		.append(" WHERE RES.DELETE_DATE IS NULL  ")
		.append(" AND TMPROOM.ROOM_NO = ?) ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, room.getRoomNo());
			log.info("ROOM_NO = " + room.getRoomNo());
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeGetDetailDrinkSQL
	 * @param reserveId
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void makeGetDetailDrinkSQL(String reserveId, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT A.DRINK_NO, B.NAME, A.QTY, A.PRICE, PRICE.PRICE AS UNIT_PRICE ")
		
		.append(" FROM DETAILDRINK A ")
		
		.append(" INNER JOIN MSTDRINK B ")
		.append(" ON B.DRINK_NO = A.DRINK_NO AND B.DELETE_DATE IS NULL ")
		
		.append(" INNER JOIN RESERVE RSV ")
		.append(" ON RSV.RESERVE_ID = A.RESERVE_ID AND RSV.DELETE_DATE IS NULL ")
		
		.append(" LEFT OUTER JOIN MSTDRINKPRICE PRICE ")
		.append(" ON PRICE.DRINK_NO = B.DRINK_NO AND PRICE.MONEY_TYPE = RSV.CURRENCY ")
		.append(" AND PRICE.DELETE_DATE IS NULL ")
		
		.append(" WHERE A.RESERVE_ID = ? ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, reserveId);
			log.info("RESERVE_ID = " + reserveId);
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeGetDetailFoodSQL
	 * @param reserveId
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void makeGetDetailFoodSQL(String reserveId, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT A.FOOD_NO, B.NAME, A.QTY, A.PRICE, PRICE.PRICE AS UNIT_PRICE ")
		
		.append(" FROM DETAILFOOD A ")
		
		.append(" INNER JOIN MSTFOOD B ")
		.append(" ON B.FOOD_NO = A.FOOD_NO AND B.DELETE_DATE IS NULL ")
		
		.append(" INNER JOIN RESERVE RSV ")
		.append(" ON RSV.RESERVE_ID = A.RESERVE_ID AND RSV.DELETE_DATE IS NULL ")
		
		.append(" LEFT OUTER JOIN MSTFOODPRICE PRICE ")
		.append(" ON PRICE.FOOD_NO = B.FOOD_NO AND PRICE.MONEY_TYPE = RSV.CURRENCY ")
		.append(" AND PRICE.DELETE_DATE IS NULL ")
		
		.append(" WHERE A.RESERVE_ID = ? ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, reserveId);
			log.info("RESERVE_ID = " + reserveId);
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * getMaxReserveNoSQL
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void getMaxReserveNoSQL(Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT MAX(RESERVE_ID) AS RESERVE_ID ")
		.append(" FROM RESERVE WHERE DELETE_DATE IS NULL ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeCreateReservationSQL
	 * @param form
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void makeCreateReservationSQL(ReserveForm form, LoginDTO user,
			Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" INSERT INTO RESERVE ( ")
		.append(" ROOM_NO, RENT_TYPE, BOOKING_YMD, BOOKING_TIME, ")
		.append(" INUSE_YMD, INUSE_TIME, FINISH_YMD, FINISH_TIME, ")
		.append(" NOTE, CUSTOMER_NO, CURRENCY, ")
		.append(" INSERT_USER_ID, INSERT_DATE, UPDATE_USER_ID, UPDATE_DATE ")
		.append(" ) VALUES ( ")
		.append(" ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, SYSDATE(), ?, SYSDATE() ")
		.append(" )")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ReserveDto dto = form.getReserveDto();
			
			ps.setString(i++, dto.getRoom().getRoomNo());
			log.info("ROOM_NO = " + dto.getRoom().getRoomNo());
			
			ps.setString(i++, dto.getRentType());
			log.info("RENT_TYPE = " + dto.getRentType());
			
			ps.setString(i++, dto.getBookingYmd());
			log.info("BOOKING_YMD = " + dto.getBookingYmd());
			
			ps.setString(i++, dto.getBookingTime());
			log.info("BOOKING_TIME = " + dto.getBookingTime());
			
			ps.setString(i++, dto.getInuseYmd());
			log.info("INUSE_YMD = " + dto.getInuseYmd());
			
			ps.setString(i++, dto.getInuseTime());
			log.info("INUSE_TIME = " + dto.getInuseTime());
			
			ps.setString(i++, dto.getFinishYmd());
			log.info("FINISH_YMD = " + dto.getFinishYmd());
			
			ps.setString(i++, dto.getFinishTime());
			log.info("FINISH_TIME = " + dto.getFinishTime());
			
			ps.setString(i++, dto.getNote());
			log.info("NOTE = " + dto.getNote());
			
			ps.setString(i++, dto.getCustomer().getCustomerNo());
			log.info("CUSTOMER_NO = " + dto.getCustomer().getCustomerNo());
			
			ps.setString(i++, dto.getCurrency());
			log.info("CURRENCY = " + dto.getCurrency());
			
			ps.setInt(i++, user.getUserId());
			log.info("INSERT_USER_ID = " + user.getUserId());
			
			ps.setInt(i++, user.getUserId());
			log.info("UPDATE_USER_ID = " + user.getUserId());
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeRegisterDetailDrinkSQL
	 * @param reserveId
	 * @param drink
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void makeRegisterDetailDrinkSQL(String reserveId,
			ReserveDetailDTO drink, LoginDTO user, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" INSERT INTO DETAILDRINK ( ")
		.append(" RESERVE_ID, DRINK_NO, QTY, PRICE, ")
		.append(" INSERT_USER_ID, INSERT_DATE, UPDATE_USER_ID, UPDATE_DATE ")
		.append(" ) VALUES ( ")
		.append(" ?, ?, ?, ?, ?, SYSDATE(), ?, SYSDATE() ")
		.append(" )")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, reserveId);
			log.info("RESERVE_ID = " + reserveId);
			
			ps.setString(i++, drink.getDetailNo());
			log.info("DRINK_NO = " + drink.getDetailNo());
			
			ps.setString(i++, drink.getQuantity());
			log.info("QTY = " + drink.getQuantity());
			
			ps.setString(i++, drink.getPrice());
			log.info("PRICE = " + drink.getPrice());
			
			ps.setInt(i++, user.getUserId());
			log.info("INSERT_USER_ID = " + user.getUserId());
			
			ps.setInt(i++, user.getUserId());
			log.info("UPDATE_USER_ID = " + user.getUserId());
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeRegisterDetailFoodSQL
	 * @param reserveId
	 * @param food
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void makeRegisterDetailFoodSQL(String reserveId,
			ReserveDetailDTO food, LoginDTO user, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" INSERT INTO DETAILFOOD ( ")
		.append(" RESERVE_ID, FOOD_NO, QTY, PRICE, ")
		.append(" INSERT_USER_ID, INSERT_DATE, UPDATE_USER_ID, UPDATE_DATE ")
		.append(" ) VALUES ( ")
		.append(" ?, ?, ?, ?, ?, SYSDATE(), ?, SYSDATE() ")
		.append(" )")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, reserveId);
			log.info("RESERVE_ID = " + reserveId);
			
			ps.setString(i++, food.getDetailNo());
			log.info("FOOD_NO = " + food.getDetailNo());
			
			ps.setString(i++, food.getQuantity());
			log.info("QTY = " + food.getQuantity());
			
			ps.setString(i++, food.getPrice());
			log.info("PRICE = " + food.getPrice());
			
			ps.setInt(i++, user.getUserId());
			log.info("INSERT_USER_ID = " + user.getUserId());
			
			ps.setInt(i++, user.getUserId());
			log.info("UPDATE_USER_ID = " + user.getUserId());
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeUpdateReservationSQL
	 * @param form
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void makeUpdateReservationSQL(ReserveForm form, LoginDTO user,
			Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE RESERVE SET ")
		.append(" RENT_TYPE = ?, ")
		.append(" NOTE = ?, ")
		.append(" CUSTOMER_NO = ?, ")
		.append(" CURRENCY = ?, ")
		.append(" UPDATE_USER_ID = ?, ")
		.append(" UPDATE_DATE = SYSDATE(), ")
		;
		
		String reserveStatus = form.getReserveDto().getReserveStatus();
		if (reserveStatus.equals(Const.SYSTEM_CONFIG.RESERVE_STATUS_BOOKING)){
			buf.append(" BOOKING_YMD = ?, ")
			.append(" BOOKING_TIME = ? ");
		} else if (reserveStatus.equals(Const.SYSTEM_CONFIG.RESERVE_STATUS_IN_USE)){
			buf.append(" INUSE_YMD = ?, ")
			.append(" INUSE_TIME = ? ");
		} else if (reserveStatus.equals(Const.SYSTEM_CONFIG.RESERVE_STATUS_FINISHED)) {
			buf.append(" FINISH_YMD = ?, ")
			.append(" FINISH_TIME = ? ");
		}
		
		buf.append(" WHERE ")
		.append(" RESERVE_ID = ? ")
		.append(" AND DELETE_DATE IS NULL ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ReserveDto dto = form.getReserveDto();
			
			ps.setString(i++, dto.getRentType());
			log.info("RENT_TYPE = " + dto.getRentType());
			
			ps.setString(i++, dto.getNote());
			log.info("NOTE = " + dto.getNote());
			
			ps.setString(i++, dto.getCustomer().getCustomerNo());
			log.info("CUSTOMER_NO = " + dto.getCustomer().getCustomerNo());
			
			ps.setString(i++, dto.getCurrency());
			log.info("CURRENCY = " + dto.getCurrency());
			
			ps.setInt(i++, user.getUserId());
			log.info("UPDATE_USER_ID = " + user.getUserId());
			
			if (reserveStatus.equals(Const.SYSTEM_CONFIG.RESERVE_STATUS_BOOKING)){
				ps.setString(i++, dto.getBookingYmd());
				log.info("BOOKING_YMD = " + dto.getBookingYmd());
				
				ps.setString(i++, dto.getBookingTime());
				log.info("BOOKING_TIME = " + dto.getBookingTime());
				
			} else if (reserveStatus.equals(Const.SYSTEM_CONFIG.RESERVE_STATUS_IN_USE)){
				ps.setString(i++, dto.getInuseYmd());
				log.info("INUSE_YMD = " + dto.getInuseYmd());
				
				ps.setString(i++, dto.getInuseTime());
				log.info("INUSE_TIME = " + dto.getInuseTime());
				
			} else if (reserveStatus.equals(Const.SYSTEM_CONFIG.RESERVE_STATUS_FINISHED)) {
				ps.setString(i++, dto.getFinishYmd());
				log.info("FINISH_YMD = " + dto.getFinishYmd());
				
				ps.setString(i++, dto.getFinishTime());
				log.info("FINISH_TIME = " + dto.getFinishTime());
			}
			
			ps.setString(i++, dto.getReserveId());
			log.info("RESERVE_ID = " + dto.getReserveId());
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeDeleteDetailSQL
	 * @param reserveId
	 * @param detail
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void makeDeleteDetailSQL(String reserveId, int detail,
			Connection conn) throws ServerException {

		String table = "";
		switch (detail) {
		case ReserveForm.DRINK:
			table = "DETAILDRINK";
			break;

		case ReserveForm.FOOD:
			table = "DETAILFOOD";
			break;
		}
		
		StringBuffer buf = new StringBuffer();
		buf.append(" DELETE FROM ").append(table)
		.append(" WHERE RESERVE_ID = ? ")		
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, reserveId);
			log.info("RESERVE_ID = " + reserveId);
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeDeleteReservationSQL
	 * @param reserveId
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void makeDeleteReservationSQL(String reserveId, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE RESERVE SET ")
		.append(" DELETE_DATE = SYSDATE() ")
		.append(" WHERE RESERVE_ID = ? ")		
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, reserveId);
			log.info("RESERVE_ID = " + reserveId);
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeChkExistBillSQL
	 * @param reserveId
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void makeChkExistBillSQL(String reserveId, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT COUNT(RESERVE_ID) AS CNT ")
		.append(" FROM BILL ")
		.append(" WHERE DELETE_DATE IS NULL AND RESERVE_ID = ? ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, reserveId);
			log.info("RESERVE_ID = " + reserveId);
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeCreateBillSQL
	 * @param reserveId
	 * @param bill
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void makeCreateBillSQL(String reserveId, BillDTO bill, LoginDTO user,
			Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" INSERT INTO BILL ( ")
		.append(" RESERVE_ID, START_YMD, START_TIME, ")
		.append(" END_YMD, END_TIME, PAY_MONEY, ")
		.append(" INSERT_USER_ID, INSERT_DATE, UPDATE_USER_ID, UPDATE_DATE ")
		.append(" ) VALUES ( ")
		.append(" ?, ?, ?, ?, ?, ?, ?, SYSDATE(), ?, SYSDATE() ")
		.append(" )")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, reserveId);
			log.info("RESERVE_ID = " + reserveId);
			
			ps.setString(i++, bill.getStartYmd());
			log.info("START_YMD = " + bill.getStartYmd());
			
			ps.setString(i++, bill.getStartTime());
			log.info("START_TIME = " + bill.getStartTime());
			
			ps.setString(i++, bill.getEndYmd());
			log.info("END_YMD = " + bill.getEndYmd());
			
			ps.setString(i++, bill.getEndTime());
			log.info("END_TIME = " + bill.getEndTime());
			
			ps.setString(i++, bill.getPayMoney());
			log.info("PAY_MONEY = " + bill.getPayMoney());
						
			ps.setInt(i++, user.getUserId());
			log.info("INSERT_USER_ID = " + user.getUserId());
			
			ps.setInt(i++, user.getUserId());
			log.info("UPDATE_USER_ID = " + user.getUserId());
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeUpdateBillSQL
	 * @param reserveId
	 * @param bill
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void makeUpdateBillSQL(String reserveId, BillDTO bill, LoginDTO user,
			Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE BILL SET ")
		.append(" START_YMD = ?, START_TIME = ?, ")
		.append(" END_YMD = ?, END_TIME = ?, PAY_MONEY = ?, ")
		.append(" UPDATE_USER_ID = ?, UPDATE_DATE = SYSDATE() ")
		.append(" WHERE RESERVE_ID = ?")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, bill.getStartYmd());
			log.info("START_YMD = " + bill.getStartYmd());
			
			ps.setString(i++, bill.getStartTime());
			log.info("START_TIME = " + bill.getStartTime());
			
			ps.setString(i++, bill.getEndYmd());
			log.info("END_YMD = " + bill.getEndYmd());
			
			ps.setString(i++, bill.getEndTime());
			log.info("END_TIME = " + bill.getEndTime());
			
			ps.setString(i++, bill.getPayMoney());
			log.info("PAY_MONEY = " + bill.getPayMoney());
			
			ps.setInt(i++, user.getUserId());
			log.info("UPDATE_USER_ID = " + user.getUserId());
			
			ps.setString(i++, reserveId);
			log.info("RESERVE_ID = " + reserveId);
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeDeleteBillSQL
	 * @param reserveId
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void makeDeleteBillSQL(String reserveId, LoginDTO user,
			Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE BILL SET ")
		.append(" DELETE_DATE = SYSDATE(), ")
		.append(" UPDATE_USER_ID = ?, UPDATE_DATE = SYSDATE() ")
		.append(" WHERE RESERVE_ID = ?")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
						
			ps.setInt(i++, user.getUserId());
			log.info("UPDATE_USER_ID = " + user.getUserId());
			
			ps.setString(i++, reserveId);
			log.info("RESERVE_ID = " + reserveId);
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}
	
}
